package edu.sjsu.cs.queryopt.dbms.main;

import java.io.File;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Hashtable;
import java.util.Scanner;

public class SelectHelper {
	static int isInListUsingIndex = 0;
	 static WordCapsule wc = new WordCapsule();
	/**
	 * 
	 * Checking the if table name is matching with value after where
	 * 
	 * @param table : table names
	 * @param where : cols name after where
	 * */
	public boolean IsWhereTableValueCorrent(ArrayList<String> table , ArrayList<String> where){

		ArrayList<String> compare = new ArrayList<String>();

			
		for(int i = 0 ; i < where.size() ; i++)
		{
			
			if(where.get(i).charAt(0) == 'T')
			{
				compare.add(getTableName(where.get(i).trim()));
			}

			
		}
		for(int ii = 0 ; ii < compare.size() ; ii++)
		{
			if(!table.contains(compare.get(ii).trim()))
			{
				System.out.println(compare.get(ii) + " is not the using in this query");
				return false;
			}
		}
		//System.out.println("Is Where " + compare);
		return true;
		
	}
	/**
	 * Checking the Cols form after select statment 
	 * 
	 * @param table: table names
	 * @param col : cols after select statement
	 * */
	public boolean IsSelectColCorrent(ArrayList<String> table , ArrayList<String> col)
	{
	//	System.out.println("table " + table);
		
		ArrayList<String>  tablenamefromCol = new ArrayList<String>();
		ArrayList<String>  compare = new ArrayList<String>();
		String tableName = " ";
			if(!col.get(0).equals("*")){
				for(int out = 0 ; out < col.size() ; out++)
				{
			    	int index = 0 ;
		
			    	while(col.get(out).charAt(index) != '.')
			    	{
			    		tableName += col.get(out).charAt(index);
			    		index++;
			    	}
			    	tablenamefromCol.add(tableName);
			    	tableName = "";
					
				}
				//System.out.println("tablenamefromCol" + tablenamefromCol );
				for(int i = 0 ; i < tablenamefromCol.size() ; i++)
				{
					if((compare.contains(tablenamefromCol.get(i))) == false)
					{
						compare.add(tablenamefromCol.get(i).trim());
					}
					
				}
				for(int i = 0 ; i < compare.size() ; i++)
				{
					
					if(!table.contains(compare.get(i)))
					{
						System.out.println("Colume " +  compare.get(i) + "  is not in the table");
						return false;
						
					}
				}
		}
		else
		{ // Case the we have to handle "*" since we need to get every col from the where statement
			// it should be true 
				return true;
		}
		return true;
	}
	/**
	 * Get the table name 
	 * */
	
    public String getTableName(String colume)
    {
    	String tableName = " ";
    	int index = 0 ;
//    	System.out.println("dsafasd " +colume );
    	while(colume.charAt(index) != '.')
    	{
    		tableName += colume.charAt(index);
    		index++;
    	}
    	
    	return tableName;    	
    }
    /**
     * Get the col number
     * */
    
    public String getColName(String col){
    	
    	String Col =  " ";
    	int index = 0 ;
    	while(col.charAt(index) != '.')
    	{
    		index++;
    	}
    	Col = col.substring(index+1, col.length());
//    	System.out.println("csscs " + Col);
    	return Col;
    	
    	
    }
    
    /***
     * Checking AccessType
     * R= TS scan
     * I= Index Scan
     * N = IN list Index scan
     * 
     * @param  al : output
     * @param con: condition pod : T1.1 = 100 something line that 
     * */
    public String AccessType(ArrayList<String> al , ArrayList<String> con , ArrayList<String> frontColpod)
    {

//    	System.out.println();
//    	

//   System.out.println("con con " + con); 	
//   System.out.println("--------Access path--------");
    	
    	/**KeyList it is contain index name
    	// remember keep the position of starting posint 
    	// for example , IndexList [T1X2, 1A, 2A, T1X3, 3A, 4A] 
    	// then we want to get 1A,2A,3A, 4A then we need to 
    	// know start point 0,3,6*/
    	ArrayList<String> KeyList = new ArrayList<String>();
    	ArrayList<Integer> remember = new ArrayList<Integer>();
    	
    	
    	ArrayList<String> IndexList = new ArrayList<String>();
    	ArrayList<String> ColCon = new ArrayList<String>();
    	/*** ht looks like... ht = {T1X3=[3A, 4A], T1X2=[1A, 2A]}*/
    	Hashtable<String, ArrayList<String>> ht = new Hashtable<String,ArrayList<String>>();
//    	System.out.println("al " + al);
    	IndexList= StringToArrayList(al);
//    	System.out.println("IndexList " + IndexList);
//    	System.out.println("frontColpod? " + frontColpod);
    	
    	
    	/**
    	 * KeyList: index name like T1X1
    	 * remember: we need to key position of Key in the IndexList
    	 * because we need to seperate key and table value later.
    	 * */
    	for(int index = 0 ; index < IndexList.size() ; index++){
    		if(IndexList.get(index).startsWith("T")){
    			KeyList.add(IndexList.get(index));
    			remember.add(index);
    		}
    	}
    	remember.add(IndexList.size());
    	
//    	System.out.println("KeyList " + KeyList);
//    	System.out.println("remember " + remember);
    	/** this look is getting all cols in the index list
    	 * then store into arraylist then insert to hashtable ht
    	 * */
    	for(int in = 0 ; in < KeyList.size() ; in++)
    	{
    		for(int out = remember.get(in) + 1 ; out < remember.get(in+1) ; out++)
    		{
    			
    			ColCon.add(IndexList.get(out));
    		}
    		ht.put(KeyList.get(in), ColCon);
    		ColCon = new ArrayList<String>();
    	}
//    	System.out.println("ht " + ht);
    	ArrayList<String> ConsitionIndex = new ArrayList<String>();
    	ArrayList<String> AfterSelect = new ArrayList<String>();
    	/**
    	 * get the table name and col name from the condition arraylist
    	 * */
    	for(int coIndex = 0 ; coIndex < con.size() ; coIndex++)
    	{
    		if(con.get(coIndex).startsWith("T"))
    		{//getTableName
    			ConsitionIndex.add(getTableName(con.get(coIndex)));
    			ConsitionIndex.add(GetColNumber(con.get(coIndex)));
    		}
    	}
    	/**
    	 * get the table anem and col name from the frontColpod
    	 * */
    	for(int foindex = 0 ; foindex < frontColpod.size() ; foindex++)
    	{
    		if(frontColpod.get(foindex).startsWith("T"))
    		{//getTableName
    			AfterSelect.add(getTableName(frontColpod.get(foindex)));
    			AfterSelect.add(GetColNumber(frontColpod.get(foindex)));
    		}
    		else if(frontColpod.get(foindex).startsWith("*"))
    		{
    			AfterSelect.add("*");
    		}
    	}
    
    	
    	if(con.contains("IN"))
    	{/**
    	 IN list case 
    	*/
//    		System.out.println("This is the In LIST");
//    		System.out.println("Thi is useing LIST prefetch");
    		//System.out.println(");
    		DBMS.PlanTable.add("N");
    		DBMS.PlanTable.add((CheckNumOfMatchCol(con, 1 , ht, KeyList))); 
    		DBMS.PlanTable.add(FindIndex(al, con));
    		if(CheckNumOfMatchCol(con, 1 , ht, KeyList).equals("0")){
    			DBMS.PlanTable.add("N");
    			/**
    			 * IN LIST CASE BUT DONT USE INDEX SO R RSAN
    			 * */
//    			DBMS.PlanTable.set(1, "R");
    			
//    			System.out.println("asDAs " + DBMS.PlanTable);

    		}
    		else{
    			if(3 ==isIndexOnlyAndIndexScan(AfterSelect , ConsitionIndex ,ht, KeyList)){
    				DBMS.PlanTable.add("N");
    			}
    			else if(1 ==isIndexOnlyAndIndexScan(AfterSelect , ConsitionIndex ,ht, KeyList)){
    				DBMS.PlanTable.add("Y");
    			}
    			
    		}
    		return "N";
    	}
    	else{
    	int check  = isIndexOnlyAndIndexScan(AfterSelect , ConsitionIndex ,ht, KeyList);
    	
    	}
    	
//    	System.out.println("ConsitionIndex " + ConsitionIndex);
//    	System.out.println("AfterSelect " + AfterSelect);
//    	System.out.println("IndexCol " + IndexCol);


//    	System.out.println("Something else");
    	return null;
    }
    
//  /*
//  * 
//  * **/
    /**
     * this fucntion is check index only and index scan
     * @param ArrayList<String> AfterSelect  : statement after select 
     * @param ArrayList<String> AfterWhere	 : statement after where
     * @param Hashtable<String, ArrayList<String>> ht  : Collection of the indexlist from output.txt
     * @param ArrayList<String> keyList : key for ht. it is the name of index
     * 
     * @return 1: index only =Y , Index scan
     * 		   2: Index only = N , Index scan
     * 		   3: Index only = N , not index scan
     * 
     * */
    public 	int isIndexOnlyAndIndexScan(ArrayList<String> AfterSelect , 
    			ArrayList<String> AfterWhere , 
    				Hashtable<String, ArrayList<String>> ht , 
    				ArrayList<String> keyList)
	 {// TODO: 1. Index list vs After select 
    	//     2. Index List vs After where
    	System.out.println();
//    	System.out.println("AfterSelect " + AfterSelect);
//    	System.out.println("AfterWhere " + AfterWhere);
//    	System.out.println("IndexList " + ht);
//    	System.out.println("KeyList " + keyList);
    	int matchcol = CheckMATCHCOL(AfterSelect, AfterWhere ,keyList, ht);
    	/**
    	 * AfterSelect is empty we assme that there was "*"
    	 * */
//    	System.out.println("AFTER AfterSelect " + AfterSelect);
//    	System.out.println("AFTER AfterSelect " + AfterWhere);
    	boolean afterselectpart = CheckIndexOnlyAndScan(AfterSelect , keyList , ht);
    	
    	boolean afterwherepart =  CheckIndexOnlyAndScan(AfterWhere, keyList, ht );
    	
    	if((afterselectpart== true) && (afterwherepart == true))
    	{
//    		System.out.println("this is index only and index scan");
//    		System.out.println("This is NOT Sequentual prefetch");
//    		System.out.println("Matching Col is " + matchcol);
    		DBMS.PlanTable.add("I");
    		DBMS.PlanTable.add(Integer.toString(matchcol));
    		DBMS.PlanTable.add(keyList.get(0));
    		DBMS.PlanTable.add("Y");
    		DBMS.PlanTable.add("Y");
    		
    		
    		return 1;
    	}
    	else if((afterselectpart == false) && (afterwherepart == false))
    	{
//    		System.out.println("This is not index only and not index scan, so R-Scan");
//    		System.out.println("This is  Sequentual prefetch");
//    		System.out.println("Matching Col is " + matchcol);
    		DBMS.PlanTable.add("R");
    		DBMS.PlanTable.add("0");
    		DBMS.PlanTable.add(" ");
    		DBMS.PlanTable.add("N");
    		DBMS.PlanTable.add("S");
    		
    		return 2;
    	}
    	else
    	{
//    		System.out.println("This is index scan but not index only");
//    		System.out.println("This is NOT Sequentual prefetch");
//    		System.out.println("Matching Col is " + matchcol);
    		DBMS.PlanTable.add("I");
    		DBMS.PlanTable.add(Integer.toString(matchcol));
    		DBMS.PlanTable.add(keyList.get(0));
    		DBMS.PlanTable.add("N");
    		DBMS.PlanTable.add("Y");
    	
    		return 3;
    	}
	 	
	 }
    /**
     * Checking the biggest match col value
     * First, collect the col number from the consition statement 
     * Second, count the number form the each index last.
     *  
     * */
    public int CheckMATCHCOL(ArrayList<String> afterSelect,
    					ArrayList<String> afterWhere , 
    						ArrayList<String> keyset ,Hashtable<String,ArrayList<String>> ht)
    {
//    	System.out.println("----CheckMATCHCOL---- ");
//    	System.out.println("afterSelect " + afterSelect);
//    	System.out.println("afterWhere " + afterWhere);
//    	System.out.println("keyset " + keyset);
//    	System.out.println("ht " + ht);
    	ArrayList<String> combineSelectNwhere = new ArrayList<String>();
    	/**
    	 * combineSelectNwhere it will combine Condition in Select and Where
    	 * because we have to count matching col from the index list
    	 * */
    	combineSelectNwhere = ComTwoArrayList(afterSelect,afterWhere );
    	//System.out.println("combineSelectNwhere " + combineSelectNwhere);
    	ArrayList<String> IndexColInList = new ArrayList<String>();
    	/**
    	 * IndexColInList: will be key for MatchColChecker hashtable
    	 * because we need to know what cols are using from the each table
    	 * it will be like
    	 * IndexColInList [T1, T2]
		 *	MatchColChecker {T2=[1, 2], T1=[3, 1, 2]}
    	 * */
    	IndexColInList =  getMatchColKey(combineSelectNwhere);
//    	System.out.println("IndexColInList " + IndexColInList);
    	Hashtable<String , ArrayList<String>> MatchColChecker = new Hashtable<String , ArrayList<String>>();
    	ArrayList<String> beforematch = new ArrayList<String>();

    	
    	

    	for(int first = 0 ; first < IndexColInList.size() ; first++){
    		
    		for(int second = 0 ; second < combineSelectNwhere.size(); second++)
    		{
    			if(IndexColInList.get(first).trim().equals(combineSelectNwhere.get(second).trim()))
    			{
    				beforematch.add(combineSelectNwhere.get(second+1));
    			}
    			
    		}
    		/**
    		 * make hashtable form like..MatchColChecker {T2=[1, 2], T1=[3, 1, 2]}
    		 * */
    		MatchColChecker.put(IndexColInList.get(first), beforematch);
    		beforematch = new ArrayList<String>();
    	}
//    	System.out.println("MatchColChecker " + MatchColChecker);
    	
    	int compare = 0;
    	int returnMactchCol = 0;
    	/**
    	 * this loop is calling NumberOfMatchCol method. it will return the matching col for each index list
    	 * then "if" statement will keep the highest number of matching col from the each index list.
    	 * */
    	for(int second = 0 ; second < IndexColInList.size() ; second++)
    	{/**
    		it is returning matching value
    	*/
    		returnMactchCol = NumberOfMatchCol(IndexColInList.get(second) , MatchColChecker.get(IndexColInList.get(second)) , ht , keyset);
    		if(compare < returnMactchCol){
    			/**
    			 * keep highest matching value
    			 * */
    			compare = returnMactchCol ;
    		}
    	}
//    	System.out.println("Matching Col  " + compare);   
    	return compare;
    }

    /**
     * NumberOfMatchCol: returning matching col of each index list.
     * @param String : it is key like T1 we need to because we want to checking the lndex list which contain same table name
     * 					like T1 is checking T1X1 , T1X2 of macthing col
     * @param ArrayList<String>: it is value in from the consition in query. 
     * SELECT T1.3 , T1.1 from T1 WHERE T1.2 = 0 AND T2.1 AND T2.2 = 0 -> [3,1,2]  or [1,2] 
     * @param Hashtable<String , ArrayList<String>> is containing <name of index ,  number of col> 
     * like  {T2X1=[1A], T1X2=[1A, 2A, 3A]}
     * @param KeySet : it is key to access data in the hashtable: MatchColChecker
     * */
    public static int NumberOfMatchCol(String key , ArrayList<String> ValueInKey ,
    								Hashtable<String , ArrayList<String>> MatchColChecker ,
    													ArrayList<String> KeySet)
    {
    	
    	int Tracker = 0; // tracking the number of matching col
    	
    	/***
    	 * Get Col number
    	 * */
    	 Hashtable<String , ArrayList<String>>  dup = new  Hashtable<String , ArrayList<String>> ();
    	 ArrayList<String> al = new ArrayList<String>();
    	 
    	
    	 // CONVERT 1A -> 1
    	 /** because it need to compare with col in the index list and col in the query condition.*/
    	 for(int third = 0 ; third  < KeySet.size() ; third++)
    	 {
    		 for(int four = 0 ; four < MatchColChecker.get(KeySet.get(third)).size() ; four++){
    			 /**
    			  * getColNum will convert 1A to 1
    			  * */
    			 String copy =getColNum(MatchColChecker.get(KeySet.get(third)).get(four));
    			 al.add(copy.trim());
    			 
    		 }
    		 /**
    		  * Dup will contain content like..dup {T2X1=[1], T1X2=[1, 2, 3]}
    		  * */
    		 dup.put(KeySet.get(third), al);
    		 al = new ArrayList<String>();
    	 }
//   	 System.out.println("dup " + dup);
//    	 
    	 /**
    	  * 
    	  * using contains method to looking for matching col in the index list
    	  * */
    	for(int first = 0 ; first < KeySet.size() ; first++)
    	{
    		if(KeySet.get(first).trim().contains(key))
    		{
    			for(int second = 0 ; second < ValueInKey.size() ; second++)
    			{
   
    				if((dup.get(KeySet.get(first)).contains(ValueInKey.get(second).trim())))
    				{
    					Tracker++;
    				}
    			}
    		}
    	}
//    	System.out.println("Tracker " + Tracker);
    	return Tracker;
    }

    
    
    
    /**
     * get key for match col [ T1, 3,  T1, 1,  T1, 2,  T2, 1] => [T1, T2]
     * @param ArrayList<String> ArrayList<String>
     * @return ArrayList<String> [T1, T2]
     * */
    public static ArrayList<String> getMatchColKey(ArrayList<String> combineSelectNwhere)
    {
    	ArrayList<String> reAl = new ArrayList<String>();
    	
    	for(int index = 0 ; index <combineSelectNwhere.size() ; index++ )
    	{
    		
    		if(combineSelectNwhere.get(index).trim().charAt(0) == 'T')
    		{

    			if(!reAl.contains(combineSelectNwhere.get(index).trim()))
    			{
    				reAl.add(combineSelectNwhere.get(index).trim());
    			}
    		}
    		
    	}
    	
    	return reAl;
    	
    }
    
    
    
    /**
     * combine two string arraylists to one arraylist
     * 
     * */
    public ArrayList<String> ComTwoArrayList (ArrayList<String>  afterSelect , ArrayList<String> afterWhere)
    {
    	 ArrayList<String> reAl = new ArrayList<String>();
    	 for(int first = 0 ; first < afterSelect.size() ; first++)
    	 {
    		 reAl.add(afterSelect.get(first));
    	 }
    	 for(int second = 0 ; second < afterWhere.size() ; second++)
    	 {
    		 reAl.add(afterWhere.get(second));
    	 }
    	 
    	 return reAl;
    	
    }
    /**
     * 
     * returning table name like T1
     * */
    public String getTableNames(String tableWithIndex)
    {
    	String reString = "";
    	for(int index = 0 ; index < tableWithIndex.length() ; index++)
    	{
    		if( tableWithIndex.charAt(index) == 'X')
    		{
    			break;
    		}
    		reString += tableWithIndex.charAt(index);
    	}
//    	System.out.println("reString " + reString);
    	return reString;
    }
    
    
    /**
     * Checking the that it can be index only or Index scan
     * 
     * @param AfterSelect: condidtion after select like  [ T1, 3,  T1, 6]
     * @param KeyList: key for hashtable  : [T1X2, T1X3]
     * @param ht: hashtable that containing all index list.. like..{T1X3=[3A, 4A], T1X2=[1A, 2A]}
     * 
     * @return if we size of tablename is equal to counter return true
     * 			else false;
     * */
    public boolean CheckIndexOnlyAndScan(ArrayList<String> AfterSelect , 
    				ArrayList<String> KeyList, Hashtable<String,
    											ArrayList<String>> ht)
    {
//    	System.out.println("After select ? "+ AfterSelect.size());
    	if(AfterSelect.contains("*"))
    	{
    		System.err.println("* doesn't contain index");
    		return false;
    	}
    	else if(AfterSelect.size() == 0){
    		System.err.println("if there is noting there is no way index scan");
    		return false;
    		
    	}
    	ArrayList<String> tablename = new ArrayList<String>(); // containing the table name after select
    	ArrayList<String> colNumber = new ArrayList<String>(); // containing the col number after select
    	ArrayList<String> preDup = new ArrayList<String>();
    	// get the table name and col from after select statement
    	for(int first = 0 ; first <AfterSelect.size(); first++ )
    	{// we know that there are even number of element
    		// because each element is based table name + col number
    		if(first%2 == 0){
    			//even part get the table name
    			tablename.add(AfterSelect.get(first));
    		}
    		else
    		{// odd part get the col number
    			colNumber.add(AfterSelect.get(first));
    		}
    	}
//    	System.out.println("After tablename " + tablename);
//    	System.out.println("After colNumber " + colNumber);
    	int Counter = 0 ;// this counter is checking that all of AfterSelect element is matching with 
    	// indexes in the index list
    	Hashtable<String , Integer> htc = new Hashtable<String, Integer>();
    	for(int second = 0 ; second < tablename.size() ; second++)
    	{/** loop for T1X3, T1X3*/ 
		
    	 	for(int third = 0 ; third < KeyList.size() ; third++)
    		{/** grap col lists from the each index*/
    	 		
//    	 		System.out.println("tablename.get(second) " + tablename.get(second));
//    	 		System.out.println("KeyList.get(third) " + KeyList.get(third));
//    	 		System.out.println("resurt  " + KeyList.get(third).trim().startsWith(tablename.get(second).trim()));
        		if(KeyList.get(third).trim().startsWith(tablename.get(second).trim()))
        		{/** if Key T1X3 is start with table name in the After select*/
        			for(int fourth = 0 ; fourth < ht.get(KeyList.get(third)).size() ; fourth++)
        			{/** checking col number in the hashtable which is col numbers in the index lists*/
        				
   				if(colNumber.get(second).trim().equals(getColNum(ht.get(KeyList.get(third)).get(fourth)).trim()))
        				{/** if it is same then it is increas counter*/
//        					System.out.println("Sae Same");
        					Counter++;

        				}
      			
        			}
        			
        		}
        		
    			htc.put(KeyList.get(third), Counter);
    			
        		Counter = 0;
        		
    		}
    	 
    	}
//    	System.out.println("htc " + htc);	
    	/**
    	 * 0: nothing
    	 * 1: index scan
    	 * 2:
    	 * */
//    	System.out.println("Counter " + Counter);
    	return (Counter == tablename.size() ? true : false);
    }
    
    public 	boolean isIndexScan(ArrayList<String> frontColPod , ArrayList<String> consitionpod)
	 {
	 	
	 	return true;
	 	
	 }
    public String FindIndexSelect(ArrayList<String> al, ArrayList<String> frontColpod ){
    	/**KeyList it is contain index name
    	// remember keep the position of starting posint 
    	// for example , IndexList [T1X2, 1A, 2A, T1X3, 3A, 4A] 
    	// then we want to get 1A,2A,3A, 4A then we need to 
    	// know start point 0,3,6*/
    	ArrayList<String> KeyList = new ArrayList<String>();
    	ArrayList<Integer> remember = new ArrayList<Integer>();
    	//ArrayList<String> ordername = new ArrayList<String>();
    	String answer = "nothing";
    	
    	ArrayList<String> IndexList = new ArrayList<String>();
    	ArrayList<String> ColCon = new ArrayList<String>();
    	/*** ht looks like... ht = {T1X3=[3A, 4A], T1X2=[1A, 2A]}*/
    	Hashtable<String, ArrayList<String>> ht = new Hashtable<String,ArrayList<String>>();
//    	System.out.println("al " + al);
    	IndexList= StringToArrayList(al);
//    	System.out.println("IndexList " + IndexList);

    	/**
    	 * KeyList: index name like T1X1
    	 * remember: we need to key position of Key in the IndexList
    	 * because we need to seperate key and table value later.
    	 * */
    	for(int index = 0 ; index < IndexList.size() ; index++){
    		if(IndexList.get(index).startsWith("T")){
    			KeyList.add(IndexList.get(index));
    			remember.add(index);
    		}
    	}
    	remember.add(IndexList.size());
    	
//    	System.out.println("KeyList " + KeyList);
//    	System.out.println("remember " + remember);
    	/** this look is getting all cols in the index list
    	 * then store into arraylist then insert to hashtable ht
    	 * */
    	boolean indextype = false;
    	boolean indexonly = false;
    	for(int in = 0 ; in < KeyList.size() ; in++)
    	{
    		for(int out = remember.get(in) + 1 ; out < remember.get(in+1) ; out++)
    		{
    			
    			ColCon.add(IndexList.get(out));
    		}
    		ht.put(KeyList.get(in), ColCon);
    		ColCon = new ArrayList<String>();
    	}

    	
    	ArrayList<Integer> numberOfIndex = new ArrayList<Integer>();
    	Hashtable<String, Integer> ht2 = new Hashtable<String,Integer>();
    	int tracker = 0;
    	
    	for(int index = 0 ; index < ht.size() ; index++){
    		for(int i = 0 ; i < frontColpod.size() ; i++){
    			String tname = getTableName(frontColpod.get(i)).trim();
    			String cname = GetColNumber(frontColpod.get(i)).trim();
    		
    			if(KeyList.get(index).trim().contains(tname))
    			{
    				
    				for(int ii = 0 ; ii < ht.get(KeyList.get(index)).size(); ii++){
    					
    					if(cname.equals(getColNum(ht.get(KeyList.get(index)).get(ii).trim()).trim())){
    						tracker++;
    						break;
    					}
    					
    				}
    				
    			}
    		}
    		ht2.put(KeyList.get(index), tracker);
    		tracker = 0;
    		
    	}
    	
    	for(int iii = 0 ; iii <KeyList.size(); iii++ ){
    		if(ht2.get(KeyList.get(iii))== frontColpod.size()){
    			// case the index only
    			DBMS.PlanTable.set(2, "0");
    			DBMS.PlanTable.set(4, "Y");
    			DBMS.PlanTable.set(5, " ");
    			break;
    		}
    		
    	}
    	
    	
//    	System.out.println("ht2 " + ht2);
    	return null;
    	
    }
    public String FindIndexOrderBy(ArrayList<String> al, ArrayList<String> orderByPod , ArrayList<String> frontColpod){
    	/**KeyList it is contain index name
    	// remember keep the position of starting posint 
    	// for example , IndexList [T1X2, 1A, 2A, T1X3, 3A, 4A] 
    	// then we want to get 1A,2A,3A, 4A then we need to 
    	// know start point 0,3,6*/
    	ArrayList<String> KeyList = new ArrayList<String>();
    	ArrayList<Integer> remember = new ArrayList<Integer>();
    	ArrayList<String> ordername = new ArrayList<String>();
    	String answer = "nothing";
    
    	for(int i = 0 ; i < orderByPod.size() ; i++){
    		if(!orderByPod.get(i).contains(",")){
    			ordername.add(orderByPod.get(i).trim());
    		}
    	}
    	
    	
    	ArrayList<String> IndexList = new ArrayList<String>();
    	ArrayList<String> ColCon = new ArrayList<String>();
    	/*** ht looks like... ht = {T1X3=[3A, 4A], T1X2=[1A, 2A]}*/
    	Hashtable<String, ArrayList<String>> ht = new Hashtable<String,ArrayList<String>>();
//    	System.out.println("al " + al);
    	IndexList= StringToArrayList(al);
//    	System.out.println("IndexList " + IndexList);

    	/**
    	 * KeyList: index name like T1X1
    	 * remember: we need to key position of Key in the IndexList
    	 * because we need to seperate key and table value later.
    	 * */
    	for(int index = 0 ; index < IndexList.size() ; index++){
    		if(IndexList.get(index).startsWith("T")){
    			KeyList.add(IndexList.get(index));
    			remember.add(index);
    		}
    	}
    	remember.add(IndexList.size());
    	
//    	System.out.println("KeyList " + KeyList);
//    	System.out.println("remember " + remember);
    	/** this look is getting all cols in the index list
    	 * then store into arraylist then insert to hashtable ht
    	 * */
    	boolean indextype = false;
    	boolean indexonly = false;
    	for(int in = 0 ; in < KeyList.size() ; in++)
    	{
    		for(int out = remember.get(in) + 1 ; out < remember.get(in+1) ; out++)
    		{
    			
    			ColCon.add(IndexList.get(out));
    		}
    		ht.put(KeyList.get(in), ColCon);
    		ColCon = new ArrayList<String>();
    	}
    	ArrayList<String> incase = new ArrayList<String>();
    	ArrayList<Integer> rememberList = new ArrayList<Integer>();
    	for(int se = 0 ; se < ordername.size(); se++){
    		
    		String tname = getTableName(ordername.get(se));
    		String cname = GetColNumber(ordername.get(se));
    		
    		for(int th = 0 ; th < KeyList.size() ; th++){
    			
    			if(KeyList.get(th).trim().contains(tname.trim())){
    				
    				for(int fo = 0 ; fo < ht.get(KeyList.get(th)).size() ; fo++){
    					String coname = getColNum(ht.get(KeyList.get(th)).get(fo));
    				
    					if(cname.trim().equals(coname.trim())){
    					
    						indextype = true;
    						answer = "i love my ex girl friend";
    						rememberList.add(th);
    					}
    				}
    			}
    			
    		}
//    		System.out.println("rememberList " + rememberList);
//    		for(){
//    			
//    		}
    		
    	}
    	
    	if(indextype == true){
    		DBMS.PlanTable.set(1, "I");
    		DBMS.PlanTable.set(4, "Y");
    		
    		/**
    		 * Since this order by is index we need to check 
    		 * this can be index only or not
    		 * */

    		if(indexonly == false){
    			DBMS.PlanTable.set(3, KeyList.get(rememberList.get(0)));
    		}
    	}
    	
//    	System.out.println("HT " + ht);
    	/**
    	 * get the table name and col name from the condition arraylist
    	 * */

    	
    	return answer;
    	
    }
    
    /**
     * Finding Index for Index scan and IN list index scan
     * @param al: is containing output file of indexlist
     * @param con: it contains condition statement after where
     * 
     * **/
    public String FindIndex(ArrayList<String> al , ArrayList<String> con ){
    	String reVal = "";
    	ArrayList<String> convertedAl = new ArrayList<String>();
    	ArrayList<String> TableNameAndColNumber = new ArrayList<String>();
    	ArrayList<Integer> rememebr =  new ArrayList<Integer>();
    	String Index = "";
    	// change  [T1X2 1A 2A , T1X3 3A 4A ] to  [T1X2, 1A, 2A, T1X3, 3A, 4A]
    	convertedAl = StringToArrayList(al);
    	int index = 0;
    	// Grap the we know the there is T1.2 list index col before "IN"
    	// We need to grap it
	    	while(!con.get(index).equalsIgnoreCase("IN")){
	    		index++;	
	    	}
	    	Index = con.get(--index); // get the index like T1.1
	//    	System.out.println("Index " + Index);
	//    	System.out.println("convertedAl " + convertedAl);
	    	//GetTableNameAndCol from this function we seprate the table name and col
	    	// ex) T1.2 -> "T1" and "2"
	    	TableNameAndColNumber = GetTableNameAndCol(Index);
//    	System.out.println("TableNameAndColNumber " + TableNameAndColNumber);
    	// checking the position of Table index 
    	// like [T1X2, 1A, 2A, T1X3, 3A, 4A] ->> [0, 3]
    	for(int first = 0 ; first < convertedAl.size() ; first++)
    	{
    		if(convertedAl.get(first).startsWith("T"))
    		{
    			rememebr.add(first);
    		}
    	}
    	// still need ending point for searching last part of cols
    	rememebr.add(convertedAl.size());
//    	System.out.println("first " + rememebr);
    	
    	for(int second = 0; second < rememebr.size() -1 ; second++)
    	{
    		if(convertedAl.get(rememebr.get(second)).contains(TableNameAndColNumber.get(0)))
    		{// if we have same table index name
    			for(int third = rememebr.get(second) + 1 ; third < rememebr.get(second+1); third++)
    			{// go to second loop to check the col number
//    				System.out.println("TableNameAndColNumber.get(1) " + TableNameAndColNumber.get(1));
//    				System.out.println("getColNum(convertedAl.get(third)) " + getColNum(convertedAl.get(third)));
    				if(TableNameAndColNumber.get(1).trim().equals(getColNum(convertedAl.get(third)).trim()))
    				{ // it is comparing the number of index col from output.txt and col number from query
    					reVal = convertedAl.get(rememebr.get(second));
    					// if it is correct return to AccessType for add in the Plantable arraylist
    					break;
    				}
    			}
    		}
    		
    	}
    	
//    	System.out.println("reVal " + reVal);
    	return reVal;
    	
    }
    /**
     * getColNum: getting column number from the 1A form
     * it only graps 1 from A1
     * 
     * */
    
    
    public static String getColNum (String colnumber)
    {
    	String number = " ";
    	int index = 0;
    	while(colnumber.charAt(index) != 'D' &&
    			colnumber.charAt(index) != 'A'
    			)
    	{
    		number += colnumber.charAt(index);
    		index++;
    	
    	}
    	return number;
    	
    }
    /**
     * @param T1.1
     * @return col number
     * **/
    public String GetColNumber(String TDotnum)
    {
    	String re =  " ";
    	for(int index = 0 ; index < TDotnum.length() ; index++)
    	{
    		if(TDotnum.charAt(index) == '.')
    		{
    			re= TDotnum.substring(index+1, TDotnum.length());
    		}
    	}
//    	System.out.println("re " + re);
    	return re;
    }
    /**
     * 
     * Get table name and col number
     * ArrayList[0] =  T1 from T1.2
     * ArrayList[1] = 2 from T1.2
     * */
    public ArrayList<String> GetTableNameAndCol (String TDotNum)
    {
//    	System.out.println("TDotNum " + TDotNum);
    	ArrayList<String> al = new ArrayList<String>();
    	String TorNum = "";
    	for(int index = 0 ; index < TDotNum.length(); index++)
    	{
    		if( TDotNum.charAt(index) == '.')
    		{
    			 al.add(TDotNum.substring(0, index));
    			 al.add(TDotNum.substring(index+1, TDotNum.length()));
    		}
    		//TorNum += TorNum.charAt(index);		
    	}
    
//    	System.out.println("GetTableNameAndCol " + al);
    	return al;
    	
    }
    
    /**
     * Check the match col
     * 
     * Checking number of mach cols
     * 
     * @param al : String ArrayList contain Consition list
     * @param type: 1. In List 2: Index Scan 
     * 
     * @return number of matching cols
     * */
    
    public String CheckNumOfMatchCol(ArrayList<String> al , int type , Hashtable<String, ArrayList<String>> ht ,ArrayList<String> keylist )
    {
    	int end = 0 ;
    	int Start = 0;
    	String ColNum = "";
    	String TableName  = "";
//    	System.out.println("CheckNumOfMatchCol " + al);
    	if(!al.get(0).trim().equals("WHERE")){
    	 ColNum=GetColNumber(al.get(0));
    	 TableName = getTableName(al.get(0));
    	}
    	else{
    		 ColNum=GetColNumber(al.get(1));
        	 TableName = getTableName(al.get(1));
    	}
//    	System.out.println("ColNum " + ColNum);
//    	System.out.println("TableName " + TableName);
//    	System.out.println("ht " + ht );
//    	System.out.println("keylist " + keylist );
    	for(int second = 0 ; second < al.size() ; second++){
			if(al.get(second).trim().equals("("))
			{// start matching col in IN list
				
				Start = second + 1;
//				System.out.println("Start " + Start);
			}
			else if(al.get(second).trim().equals(")"))
			{// end matching col in IN list
				end = second;
//				System.out.println("end " + end);
			}
			
			
    	}
    	end = end - Start;
//    	System.out.println("end " + end);
    	if(type == 1)
    	{// IN list 
    		for(int index = 0 ; index < keylist.size() ; index++)
    		{
    			
    			if(keylist.get(index).trim().contains(TableName.trim()))
    			{
    				for(int inner = 0 ; inner < ht.get(keylist.get(index)).size(); inner++){
//    					System.out.println("getColNum(ht.get(keylist.get(index)).get(inner).trim()) " + getColNum(ht.get(keylist.get(index)).get(inner).trim()));
    					if(ColNum.trim().equals(getColNum(ht.get(keylist.get(index)).get(inner).trim()).trim()))
    					{
//    						System.out.println("I found " + end);
    						return Integer.toString(end);
    					}
    					
    				}
    			}

    		}
    		
    		
    	}
    	return Integer.toString(0);
    
    //	System.out.println("Start " + Start);
    	// convert int number to string for plantable list
    	
    	
    }
    
    /**
     * Get the output value and put into String ArrayList
     * like... al = {"hello bob bye"} to al= {"hello" , "bob" , ...}
     * @param al: some string array with sentances
     * @return some string arraylist with only trim word
     * **/
    public ArrayList<String> StringToArrayList(ArrayList<String> al)
    {
    	ArrayList <String> returnAl = new ArrayList<String>();
    	String first = " ";
    	for(int index = 0 ; index < al.size() ; index++)
    	{
    		 first += al.get(index) + " ";
    		
    	}
    	String[] tokens = first.trim().split(" ");
    	returnAl.addAll(Arrays.asList(tokens));
    	
    	return returnAl;
    	
    	
    	
    }
    public int GetCardinality(ArrayList<String> frontcolpod , ArrayList<String> tablepod
    			, ArrayList<String> conditionpod) throws FileNotFoundException
    {
    	
    	// ok devide by single or two table
//    	System.out.println("It is in the get Card");
    	ArrayList<String> AllTableContainer = new ArrayList<String>();
    	ArrayList<String> ConCol = new ArrayList<String>();
    	if(tablepod.size() == 1)
    	{// using single table
    		AllTableContainer = CreateTable.MappingOrignalTable(tablepod.get(0)+WordCapsule.tablefiletype);
//    		System.out.println("AllTableContainer " + AllTableContainer);
    		if(frontcolpod.get(0).equals("*"))
    		{// select all
    			
    			CreateTable.NumberOfCalIntable(tablepod.get(0)+WordCapsule.tablefiletype);
    		}
    		else
    		{// or select by fortcolpad
    			
    		}
    		
    	}
    	else
    	{// using more than one table
    		
    		
    	}
    	
    	return 0;
    	
    }

    
    public boolean IsOrderBy(ArrayList<String> con){
    	
//    	for(int index = 0 ; index < con.size() ; index++){
	    	if(con.contains("ORDER") && con.contains("BY") ){
	    		System.out.println("This is order by");
	    		return true;
	    	}
	    	else{
	    		System.out.println("this is not order by");
	    		return false;
	    	}
//    	}
//		return false;
    	
    	
    	
    }
    public ArrayList<String> ArrayToArrayList(String [] sa)
    {
    	ArrayList<String> real = new ArrayList<String>();
    	
    	for(int index = 0 ; index < sa.length ; index++)
    	{
    		real.add(sa[index]);
    	}
    	
    	return real;
    }
    public ArrayList<String> getIndexList() throws FileNotFoundException{
    	
    	Scanner outputFile = new Scanner(new File(wc.indextb));
    	ArrayList<String> outputContainer = new ArrayList<String>();
    	while(outputFile.hasNextLine())
    	{
    		String IndexListLine = outputFile.nextLine();
    		outputContainer.add(IndexListLine.trim());		                           		                              		                                		
    	}
    	return outputContainer;
    }


}
